﻿-- =============================================
-- Author:		Taylor Chase and Jon Stocksdale
-- Create date: March 3, 2016
-- Description:	Takes an entity type (and possibly subtype) and returns a query that can be executed with columns representing all attributes that belong to the specified entity type
-- =============================================
CREATE FUNCTION [dbo].[ConvertRafikiNOSQL_EntityView] 
(
    @EntityType varchar(max),
	@EntitySubType varchar(max) = NULL
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @strcols AS NVARCHAR(MAX), @numcols AS NVARCHAR(MAX), @datecols AS NVARCHAR(MAX), @bitcols AS NVARCHAR(MAX), @bincols AS NVARCHAR(MAX),
@query as NVARCHAR(MAX),@allcols AS NVARCHAR(MAX), @derivedcols AS NVARCHAR(MAX)
DECLARE @EntityTypeName nvarchar(255),@JunkInt int,@LookupName NVARCHAR(MAX)
	
	IF @EntitySubType is null BEGIN
		select @EntityTypeName=DefinedEntityName from DefinedEntityTypes where EntityType = @EntityType
	END ELSE BEGIN
		select @EntityTypeName=DefinedEntityName from DefinedEntitySubTypes where EntityType = @EntityType AND EntitySubType = @EntitySubtype
		IF @EntityTypeName IS NULL
			select @EntityTypeName=DefinedEntityName from DefinedEntityTypes where EntityType = @EntityType
	END

  	select @strcols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from DefinedAttributes WHERE EntityType = @EntityType AND (EntitySubType = @EntitySubType OR EntitySubType is null)
						AND DataType = 'String'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	select @numcols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from DefinedAttributes WHERE EntityType = @EntityType AND (EntitySubType = @EntitySubType OR EntitySubType is null)
						AND DataType = 'Number'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	select @datecols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from DefinedAttributes WHERE EntityType = @EntityType AND (EntitySubType = @EntitySubType OR EntitySubType is null)
						AND DataType = 'Date'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	select @bitcols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from DefinedAttributes WHERE EntityType = @EntityType AND (EntitySubType = @EntitySubType OR EntitySubType is null)
						AND DataType = 'Boolean'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	select @bincols = STUFF((SELECT  ',' + QUOTENAME(AttributeName) 
						from DefinedAttributes WHERE EntityType = @EntityType AND (EntitySubType = @EntitySubType OR EntitySubType is null)
						AND DataType = 'Binary'
						group by  AttributeName 
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')

	IF @EntitySubType is null BEGIN
		set @query = 'SELECT * FROM (SELECT DISTINCT EntityID from Entities WHERE EntityType = ''' + @EntityType + ''') AS mainatt'
	END ELSE BEGIN
		set @query = 'SELECT * FROM (SELECT DISTINCT EntityID from Entities WHERE EntityType = ''' + @EntityType + ''' AND EntitySubType = ''' + @EntitySubType + ''') AS mainatt'
	END
	set @allcols = ''

	IF @strcols IS NOT NULL
	BEGIN
		set @query = @query +
				' LEFT OUTER JOIN (SELECT EntityID as EID1, ' + @strcols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueString)
					for AttributeName in (' + @strcols + ')
				) a WHERE DataType=''' + 'String' + ''') AS stratt ON mainatt.EntityID = stratt.EID1'
		set @allcols = @allcols + ', ' + @strcols
	END
	IF @numcols IS NOT NULL
	BEGIN
		set @query = @query +
				' LEFT OUTER JOIN (SELECT EntityID as EID2, ' + @numcols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueNumber)
					for AttributeName in (' + @numcols + ')
				) a WHERE DataType=''' + 'Number' + ''') AS numatt ON mainatt.EntityID = numatt.EID2'
		set @allcols = @allcols + ', ' + @numcols
	END

	IF @datecols IS NOT NULL
	BEGIN
		set @query = @query +
				' LEFT OUTER JOIN (SELECT EntityID as EID3, ' + @datecols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueDateTime)
					for AttributeName in (' + @datecols + ')
				) a WHERE DataType=''' + 'Date' + ''') AS dateatt ON mainatt.EntityID = dateatt.EID3'
		set @allcols = @allcols +  ', ' + @datecols
	END
	IF @bitcols IS NOT NULL
	BEGIN
		set @query = @query +
				' LEFT OUTER JOIN (SELECT EntityID as EID4, ' + @bitcols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueBit)
					for AttributeName in (' + @bitcols + ')
				) a WHERE DataType=''' + 'Boolean' + ''') AS bitatt ON mainatt.EntityID = bitatt.EID4'
		set @allcols = @allcols +  ', ' + @bitcols
	END
	IF @bincols IS NOT NULL
	BEGIN
		set @query = @query +
				' LEFT OUTER JOIN (SELECT EntityID as EID5, ' + @bincols + '
					from AttributesWithAttributeNames
				pivot 
				(
					max(AttributeValueDateTime)
					for AttributeName in (' + @bincols + ')
				) a WHERE DataType=''' + 'Binary' + ''') AS binatt ON mainatt.EntityID = binatt.EID5'
		set @allcols = @allcols +  ', ' + @bincols
	END
	  	select @derivedcols = STUFF((SELECT  ',' + [Format] + ' AS ' + [DerivedAttributesName]
						from DefinedDerivedAttributes WHERE EntityType = @EntityType AND (EntitySubType = @EntitySubType OR EntitySubType is null)
						group by  DerivedAttributesName, [Format]
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')
	IF @derivedcols IS NOT NULL
	BEGIN
			set @allcols = @allcols +  ', ' + @derivedcols
	END

	SELECT @JunkInt = COUNT(*) FROM DefinedDerivedAttributes WHERE EntityType = @EntityType 
		AND (EntitySubType = @EntitySubType OR EntitySubType is null) 
		AND DerivedAttributesName = 'LookupName'
	IF @JunkInt = 0
		SET @LookupName = ' NULL AS LookupName, '
	ELSE
		SET @LookupName = ''


	set @query = 'SELECT ' + @EntityTypeName + ' as EntityName,' + @LookupName + '* 
					FROM (SELECT EntityID' + @allcols + ' 
					FROM (' + @query + ') as qt) as final'

	RETURN @query

END